# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/4/2 10:21
# @Author  : qiaoc
# @File    : AuditWriteOff.py
# @Desc    : 移动电费核销稽核模块

import os
from ocr.Ocr import *
from dao.PyMysql import *
from affix import AffixUtils
from examine.po.AuditResult import *
from examine.rule import StampRule
from examine.rule import AuditRule


# 不用处理的标杆类型
# no_need_to_deal_with_benchmark_type = ['智能电表标杆（动环）', '智能电表标杆（集采）', '动环负载标杆', '平峰谷均价标杆（录平峰谷表采用总量录入方式时）']
no_need_to_deal_with_benchmark_type = ['智能电表标杆（动环）', '智能电表标杆（集采）', '动环负载标杆']



"""
移动电费核销稽核模块
"""

# 核销类型
def audit_write_off(bill_number, floder_path, type):
    """ success == 0 成功通过稽核；\r\n
                success == 1 转人工\r\n
                success == 2 稽核不通过
              """
    AffixUtils.file_classification(floder_path)
    file_flag1 = True  # 判断附件是否存在
    file_flag2 = True
    file_list1 = []
    file_list2 = []
    stamp1 = []  # 印章识别信息
    stamp2 = []
    rar_file = []
    rp_agreement_info_tablename = 'rp_agreement_info'  # 报账点与合同信息
    rp_verification_info_tablename = 'rp_verification_info'  # 报账点缴费信息
    verification_electric_info_tablename = 'verification_electric_info'  # 其他信息
    verification_detail_info_tablename = 'verification_detail_info'  # 电表缴费明细信息
    verification_benchmark_info_tablename = 'verification_benchmark_info'  # 缴费标杆信息
    audit_refused_type = {}  # 审核不通过类型
    auditResult = AuditResult()
    audit_list = []  # 稽核结果集，需存库
    result = {'success': 0, 'message': '通过'}
    try:
        rp_verification_info_sql = """SELECT * FROM """ + rp_verification_info_tablename + """ WHERE verification_code='""" + bill_number + """' and recycle_id = '0' """
        rp_verification_info_data = PyMysql().get_db_data(rp_verification_info_sql)
        page_business_activities = rp_verification_info_data[0]['business_activities']

        # 判断该工单号是否存在多块电表
        whether_execute = False  # 是否执行多块电表
        verification_detail_info_sql = """SELECT * FROM """ + verification_detail_info_tablename + """ WHERE verification_code='""" + bill_number + """' and recycle_id = '0'"""
        verification_detail_info_data = PyMysql().get_db_data(verification_detail_info_sql)

        execute_list = [] # 户号和对应取值行数
        # 核对数据库是否存在工单数据，以及是否为转供电
        execute_list, auditResult, audit_list = first_audit(page_business_activities, auditResult, audit_list,
                    bill_number, whether_execute, verification_detail_info_data, execute_list)

        if len(audit_list) == 0:
            # all_file_name = os.listdir(floder_path)
            all_file = AffixUtils.get_all_attachment(floder_path)
            affix_list_from_db = AffixUtils.get_affix_from_db(bill_number)
            """
                            判断上传的附件是否存在，是否缺少附件
            """
            audit_list, auditResult = reco_affix_is_exist(all_file, floder_path, auditResult,
                                                          audit_list, bill_number, affix_list_from_db)
            all_ocr_attachment, rar_file = AffixUtils.get_curing_ocr_attachment(floder_path)
            # 第一步：印章识别
            audit_list, auditResult = StampRule.audit_rule_verific_stamp(all_ocr_attachment,rar_file,auditResult,audit_list,bill_number,
                         file_flag1,affix_list_from_db,file_list1,stamp1,file_flag2,
                         file_list2,stamp2)
            # 第二步：查询数据库中的页面信息
            # verification_detail_info_data = []  # 多块电表缴费明细信息-数据列表
            if whether_execute == True:
                for es_number_value in execute_list:
                    verification_detail_info_sql = """SELECT * FROM """ + verification_detail_info_tablename + """ WHERE payment_code='""" + bill_number + """' and es_number='""" + str(es_number_value[0]) + """' and recycle_id = '0' """
                    verification_detail_esNumber_info_data = PyMysql().get_db_data(verification_detail_info_sql)
                    verification_detail_info_data.append(verification_detail_esNumber_info_data[int(es_number_value[1])])
            else:
                verification_detail_info_sql = """SELECT * FROM """ + verification_detail_info_tablename + """ WHERE verification_code='""" + bill_number + """' and recycle_id = '0' """
                verification_detail_info_data = PyMysql().get_db_data(verification_detail_info_sql)

            # 若查无数据则下面的代码会报异常，走后面的异常处理
            page_es_number = verification_detail_info_data[0]['es_number']  # 页面抓取的电表户号
            es_number = page_es_number
            page_current_share_ratio = verification_detail_info_data[0]['current_share_ratio']  # 本次实际分摊比例
            page_current_share_ratio = re.findall(r"\d+\.?\d*", str(page_current_share_ratio).replace('%', ''))[0]
            page_current_reading = verification_detail_info_data[0]['current_reading']  # 本期读数（度）
            page_last_reading = verification_detail_info_data[0]['last_reading']  # 上期读数（度）
            select_page_ele_info_data = PyMysql().select_eleinfo(verification_detail_info_tablename, 'verification_code',
                                                       bill_number)
            page_electricity_price = []
            page_electricity_consumption = []
            page_electricity_loss = []
            if whether_execute == False:
                if select_page_ele_info_data and len(select_page_ele_info_data):
                    page_electricity_price = select_page_ele_info_data[0]  # 电费单价（元/度）
                    page_electricity_consumption = select_page_ele_info_data[1]  # 用电量（度）
                    try:
                        page_electricity_loss = select_page_ele_info_data[2]  # 电损电量
                    except:
                        page_electricity_loss = ['0']  # 电损电量
            page_share_degrees = verification_detail_info_data[0]['share_degrees']  # 分摊后度数
            es_magnification = verification_detail_info_data[0]['es_magnification']  # 电表倍率
            verification_electric_info_sql = """SELECT * FROM """ + verification_electric_info_tablename + """ WHERE verification_code='""" + bill_number + """' and recycle_id = '0' """
            verification_electric_info_data = PyMysql().get_db_data(verification_electric_info_sql)
            rp_code = verification_electric_info_data[0]['rp_code']  # 报账点编码
            page_actual_total_amount = verification_electric_info_data[0]['actual_total_amount']  # 实际总金额
            page_actual_verification_amount = verification_electric_info_data[0]['actual_verification_amount']  # 实际核销金额
            page_payment_amount = verification_electric_info_data[0]['payment_amount']  # 支付金额
            rp_agreement_info_sql = """SELECT * FROM """ + rp_agreement_info_tablename + """ WHERE rp_code='""" + rp_code + """'"""
            rp_agreement_info_data = PyMysql().get_db_data(rp_agreement_info_sql)
            page_rp_name = rp_agreement_info_data[0]['rp_name']  # 报账点名称

            page_actual_nae_taxsad = rp_verification_info_data[0]['actual_nae_taxsad']  # 实际不含税电费金额
            page_actual_ef_tariff = rp_verification_info_data[0]['actual_ef_tariff']  # 实际电费税金
            page_actual_reimbursement_amount = rp_verification_info_data[0]['actual_reimbursement_amount'] # 【其他费用】的【含税金额】(实际报账金额)
            page_amount_including_tax = verification_electric_info_data[0]['amount_including_tax'] #总含税金额
            page_start_time = rp_verification_info_data[0]['start_time']  # 核销期始
            page_end_time = rp_verification_info_data[0]['end_time']  # 核销期终
            page_actual_total_pc = rp_verification_info_data[0]['actual_total_pc']  # 实际总耗电量
            verification_benchmark_info_sql = """SELECT * FROM """ + verification_benchmark_info_tablename + """ WHERE verification_code='""" + bill_number + """' and recycle_id = '0' """
            verification_benchmark_info_data = PyMysql().get_db_data(verification_benchmark_info_sql)  # 缴费标杆信息

            auditResult, audit_list, audit_refused_type = AuditRule.audit_rule_verification_page(auditResult, audit_list, bill_number, audit_refused_type,
                                         page_actual_nae_taxsad, page_actual_ef_tariff, page_amount_including_tax,
                                         page_actual_total_amount,
                                         page_actual_verification_amount, page_payment_amount, whether_execute,
                                         verification_detail_info_data, page_current_share_ratio, page_es_number,
                                         page_actual_reimbursement_amount, page_actual_total_pc,
                                         page_electricity_loss, page_share_degrees,
                                         all_file, affix_list_from_db, floder_path, verification_benchmark_info_data,rp_code)
            # 第三步：查询数据库中保存的OCR数据
            att_2_name = all_file['附件2']
            if '汇总' in att_2_name:
                if whether_execute == True:
                    ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE affixx_name='""" + att_2_name + """' """
                else:
                    ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE uid='""" + es_number + """' and affixx_name='""" + att_2_name + """' """
            else:
                ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE bill_number='""" + bill_number + """' """
            bill_table_data = PyMysql().get_db_data(ocr_curing_sql)
            if not bill_table_data and len(bill_table_data) == 0:
                # 找不到，则OCR所有的图片，存库
                # todo : 区分附件进行识别，存储每个附件的识别异常结果
                try:
                    Ocr().ocr_img_list(all_ocr_attachment, bill_number, type,1)
                except Exception as e:
                    pass
            bill_table_data = PyMysql().get_db_data(ocr_curing_sql)
            if not bill_table_data and len(bill_table_data) == 0:
                auditResult, audit_list = AuditRule.audit_rule_verification_no_data(auditResult, audit_list, rar_file, bill_number, affix_list_from_db,
                                                whether_execute,
                                                page_rp_name, page_start_time, page_end_time, page_actual_total_amount,
                                                page_current_reading, page_electricity_price,
                                                page_electricity_consumption, page_electricity_loss,
                                                verification_detail_info_data, file_flag2)

            else:
                ocr_uname = bill_table_data[0]['uname']  # 客户地址
                if ocr_uname:
                    ocr_uname = ocr_uname.replace(' ','')
                ocr_ele_charge = bill_table_data[0]['ele_charge']  # 电费金额
                ocr_now_display_num = bill_table_data[0]['now_display_num']  # 【本期指示】/【本月】/本期读数
                if ocr_now_display_num:
                    ocr_now_display_num = round(float(ocr_now_display_num.replace(' ', '')), 2)
                else:
                    ocr_now_display_num = 0
                ocr_ele_price = bill_table_data[0]['ele_price']  # 【电价】/【电费信息】
                if not ocr_ele_price:
                    ocr_ele_price = 0
                ocr_total_power = bill_table_data[0]['total_power']  # 总结算电量
                if not ocr_total_power:
                    ocr_total_power = 0
                ocr_ele_loss = bill_table_data[0]['ele_loss']  # 电损值
                ocr_last_display_num = bill_table_data[0]['last_display_num']  # 上期读数
                ocr_start_date = bill_table_data[0]['start_date']  # 开始时间
                ocr_end_date = bill_table_data[0]['end_date']  # 结束时间

                bill_table_data_list = []  # 多块电表，户号对应的识别结果 结构[[户号1，识别结果]，[户号2，识别结果]]
                for data in verification_detail_info_data:
                    ocr_curing_sql = ''
                    if '汇总' in att_2_name:
                        ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE uid='""" + data[
                            'es_number'] + """' and affixx_name='""" + att_2_name + """' """
                    else:
                        ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE bill_number='""" + bill_number + """' """
                    ocr_table_data = PyMysql().get_db_data(ocr_curing_sql)
                    if not ocr_table_data and len(ocr_table_data) == 0:
                        bill_table_data_list.append([data['es_number'], ''])
                    else:
                        if not ocr_table_data[0]['ele_price']: # 【电价】/【电费信息】
                            ocr_table_data[0]['ele_price'] = 0
                        if not bill_table_data[0]['total_power']: # 总结算电量
                            ocr_table_data[0]['total_power'] = 0
                        bill_table_data_list.append([data['es_number'], ocr_table_data[0]])
                """
                    【核销单】-数据稽核
                """
                auditResult, audit_list, audit_refused_type, result = AuditRule.audit_rule_verification_data(
                                                             audit_list, auditResult, bill_number, rar_file, whether_execute, audit_refused_type,result,
                                                             file_flag2, page_rp_name, affix_list_from_db,ocr_uname,
                                                             page_actual_total_amount, ocr_ele_charge, verification_detail_info_data,page_current_reading,ocr_now_display_num,
                                                             data,page_electricity_price,ocr_ele_price, page_electricity_consumption,page_last_reading,ocr_last_display_num,
                                                             ocr_ele_loss,page_current_share_ratio,bill_table_data_list, ocr_total_power,
                                                             page_electricity_loss,page_start_time, page_end_time,ocr_start_date, ocr_end_date,es_magnification)
        # 存储稽核信息到数据库中
        # try:
        PyMysql.insert_data(PyMysql().nf_audit_point_tablename, PyMysql().nf_audit_point_table_key, audit_list)

        subAuditStatus = getSubAuditStatus(audit_list)
        # 更新工单审核信息
        is_artificial = 1  # 是否转人工
        if subAuditStatus == '审核中':
            is_artificial = 0
        elif subAuditStatus == '不通过':
            # 不通过类型插入数据库动作
            PyMysql().ergodic_Noresult_audit_list(audit_list)
        if subAuditStatus == '通过':
            PyMysql().update_nf_audit_info(str(bill_number), subAuditStatus, '')

        audit_refused_type_str = ''
        for t in audit_refused_type:
            if t:
                audit_refused_type_str = audit_refused_type_str + ',' + str(t)
            else:
                audit_refused_type_str = str(t)
        audit_remark = ''  # 审核意见
        for po in audit_list:
            if audit_remark:
                if "合格" in str(po[6]):
                    continue
                audit_remark = audit_remark + ',' + str(po[6])
            else:
                if "合格" in str(po[6]):
                    continue
                audit_remark = str(po[6])
        auditResult.message = audit_remark
        type_ele = '报账点核销审核'
        save_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        update_sql = """UPDATE """ + PyMysql().sys_audit_info_tablename + """ SET audit_status='""" + \
                     str(subAuditStatus) + """',audit_refused_type='""" + str(
            audit_refused_type_str) + """',audit_rd_type='""" + str(audit_remark) + """',is_artificial='""" + \
                     str(is_artificial) + """',save_time='""" + str(
            save_time) + """' WHERE payment_code='""" + str(
            bill_number) + """' AND document_type='""" + str(type_ele) + """'"""
        PyMysql().execute_sql(update_sql)
        rpv_update_sql = """ update rp_verification_info SET audit_status='""" + str(subAuditStatus) + """' where verification_code = '""" + str(bill_number) + """' """
        # print(rpv_update_sql)
        PyMysql().execute_sql(rpv_update_sql)
        # except Exception as e:
        #     pass
    except Exception as e:
        import traceback
        traceback.print_exc()
        # print(e,e.__traceback__.tb_lineno)
        result['success'] = 1
        result['message'] = 'RPA处理异常'
    return result

def getSubAuditStatus(audit_list):

    no_audit_list = ['总金额（其他费用）','总金额（核销/支付）','实际总金额','核销期','分摊比例','本期读数','电费单价','上期读数','用电量','电损电量']
    artificial_list = ['平均电费单价','电损电量占比','超标说明']
    audit_status_list = []
    for auditInfo in audit_list:
        if auditInfo[1] in no_audit_list and auditInfo[4] == '0':
            audit_status_list.append('不通过')
        if auditInfo[1] in artificial_list and auditInfo[4] == '0':
            audit_status_list.append('审核中')

    if audit_list:
        if '不通过' in audit_status_list:
            return '不通过'
        if '审核中' in audit_status_list:
            return '审核中'
    return '通过'


def first_audit(page_business_activities,auditResult,audit_list,
                    bill_number,whether_execute,verification_detail_info_data,execute_list):
    if len(verification_detail_info_data) > 1:
        es_number_list = []  # 户号列表（该单据所有户号包含重复户号）
        es_number_value_list = []  # 户号内容列表（该单据所有户号包含重复户号）
        for content in verification_detail_info_data:
            es_number_list.append(content['es_number'])
            es_number_value_list.append(
                [content['es_number'], content['electricity_consumption'], content['electricity_loss']])
        # 判断es_number_list是否有相同户号
        set_es_number_list = set(es_number_list)
        if len(es_number_list) == len(set_es_number_list):
            # 　多块电表不同户号场景
            whether_execute = True
        else:
            execute_list = PyMysql().select_scene('verification_detail_info', 'verification_code', bill_number,
                                                  set_es_number_list)
            if len(execute_list) == len(set_es_number_list):
                whether_execute = True
            else:
                print('多块电表存在相同户号且多行数据用电量和电损电量不为0')

    if page_business_activities and '转供电' in page_business_activities:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '供电类型判断', page_business_activities, '', '0', 'RPA',
                           '页面【报账点核销信息】-【业务活动】存在“转供电”直接转人工不作稽核', '', '1', ''])
    elif whether_execute == False and len(verification_detail_info_data) > 1:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '电表类型判断', page_business_activities, '', '0', 'RPA',
                           '多块电表存在相同户号含多行数据用电量和电损电量不为0', '', '1', ''])
    elif len(verification_detail_info_data) == 0:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '电表类型判断', page_business_activities, '', '0', 'RPA',
                           '【电表缴费明细信息】为空', '', '1', ''])
    return execute_list, auditResult, audit_list


def reco_affix_is_exist(all_file,floder_path,auditResult,audit_list,bill_number,affix_list_from_db):
    a_1, a_2, a_3, a_4, a_5, a_6 = 0, 0, 0, 0, 0, 0
    for f in all_file:
        if '附件1' in all_file[f]:
            a_1 = 1
        if '附件2' in all_file[f]:
            a_2 = 1
        if '附件3' in all_file[f]:
            a_3 = 1
        if '附件4' in all_file[f]:
            a_4 = 1
        if '附件5' in all_file[f]:
            a_5 = 1
        if '附件6' in all_file[f] and 'xlsx' in all_file[f]:
            a_6 = 1
            table_sql = """SELECT * FROM rpa_standard_site WHERE affixx_name='""" + all_file[f] + """'"""
            standard_table_data = PyMysql().get_db_data(table_sql)
            if not standard_table_data and len(standard_table_data) == 0:
                path = floder_path + '//附件6//' + all_file[f]
                AffixUtils.excel_to_data(path, all_file[f])
    if a_1 == 0 or a_2 == 0 or a_3 == 0:
        if a_1 == 0:
            auditResult.auditStatus = '审核中'  # 转人工
            file_flag1 = False
            audit_list.append([str(bill_number), '发票印章', '“电力”或“供电”', '', '0', 'RPA', '附件1发票不存在',
                               AffixUtils.get_affix_url_with_name(affix_list_from_db, '附件1'), '5', ''])
        if a_2 == 0:
            auditResult.auditStatus = '审核中'  # 转人工
            file_flag2 = False
            audit_list.append([str(bill_number), '电力明细印章', '“电力”或“供电”', '', '0', 'RPA', '附件2电力明细不存在',
                               AffixUtils.get_affix_url_with_name(affix_list_from_db, '附件2'), '5', ''])
    return audit_list, auditResult


